package com.his.system.manager.task;

/**
 * SSS
 *
 * @Description
 * @Author HHJ
 * @Date 2020-07-25 16:36
 */

import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@Component
public class JdbcUserDao {

    private static final String driverClassName = "com.mysql.jdbc.Driver";
    //private static final String url = "jdbc:mysql://localhost:3306/db_his_system?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8";
    //private static final String username = "root";
    //private static final String password = "root";
    //阿里云服务
    private static final String url = "jdbc:mysql://47.115.14.28:3306/popmanager_new?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8";
    private static final String username = "root";
    private static final String password = "mysql1qaz#EDC";

    public void add(UserDto form) {
        Connection conn = null;
        //使用预编译语句
        PreparedStatement pstmt = null;
        //获取的结果集
        ResultSet res = null;
        String sqlStr = "INSERT INTO `user` ( UserName, Password, UseType, RealName,DeptID,DelMark) VALUES(?,?,?,?,?,?)";

        try {
            //执行驱动
            Class.forName(driverClassName);
            //获取连接
            conn = DriverManager.getConnection(url, username, password);
            //设置的预编译语句格式
            pstmt = conn.prepareStatement(sqlStr);
            pstmt.setString(1, form.getUsername());
            pstmt.setString(2, form.getPassword());
            pstmt.setInt(3, form.getAge());
            pstmt.setString(4, form.getRealName());
            pstmt.setInt(5, form.getDeptID());
            pstmt.setInt(6, 1);
            pstmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源,倒关
            try {
                if (res != null) res.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
            }
        }


    }

    public List<EntryRecord> getEntryRecordList(int pageNo, int pageSize) {
        Connection conn = null;
        //使用预编译语句
        PreparedStatement pstmt = null;
        //获取的结果集
        ResultSet res = null;

        List<EntryRecord> entryRecordList = new ArrayList<>();
        String sqlStr = "SELECT * FROM `pis_entryrecord` limit " + pageNo + "," + pageSize;
        try {
            //执行驱动
            Class.forName(driverClassName);
            //获取连接
            conn = DriverManager.getConnection(url, username, password);

            pstmt = conn.prepareStatement(sqlStr);
            res = pstmt.executeQuery();
            while (res.next()) {
                EntryRecord entryRecord = new EntryRecord();
                entryRecord.setRecordID(res.getString("RecordID"));
                entryRecord.setDeviceNo(res.getString("DeviceNo"));
                entryRecord.setCardType(res.getInt("CardType"));
                entryRecord.setCardNum(res.getString("CardNum"));
                entryRecord.setRecordTime(res.getString("RecordTime"));
                entryRecord.setCreateTime(res.getString("CreateTime"));
                entryRecord.setMsg(res.getString("Msg"));
                entryRecord.setRecordType(res.getInt("RecordType"));
                entryRecord.setId(res.getInt("ID"));

                entryRecordList.add(entryRecord);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源,倒关
            try {
                if (res != null) res.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
            }
        }
        return entryRecordList;
    }

    public List<UserDto> selectList() {
        Connection conn = null;
        //使用预编译语句
        PreparedStatement pstmt = null;
        //获取的结果集
        ResultSet res = null;

        List<UserDto> userList = new ArrayList<>();
        String sqlStr = "SELECT * FROM `user` ";
        try {
            //执行驱动
            Class.forName(driverClassName);
            //获取连接
            conn = DriverManager.getConnection(url, username, password);

            pstmt = conn.prepareStatement(sqlStr);
            res = pstmt.executeQuery();
            while (res.next()) {
                UserDto user = new UserDto();
                user.setUserId(res.getInt("id"));
                user.setUsername(res.getString("UserName"));
                user.setPassword(res.getString("Password"));
                user.setAge(res.getInt("UseType"));
                user.setRealName(res.getString("RealName"));
                user.setDeptID(res.getInt("DeptID"));
                userList.add(user);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源,倒关
            try {
                if (res != null) res.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
            }
        }
        return userList;
    }

    public UserDto findById(String userId) {
        Connection conn = null;
        //使用预编译语句
        PreparedStatement pstmt = null;
        //获取的结果集
        ResultSet res = null;

        String sqlStr = "SELECT * FROM `user` WHERE `id`=?";
        try {
            //执行驱动
            Class.forName(driverClassName);
            //获取连接
            conn = DriverManager.getConnection(url, username, password);

            pstmt = conn.prepareStatement(sqlStr);
            pstmt.setNString(1, userId);
            res = pstmt.executeQuery();
            if (res == null) {
                return null;
            }
            if (res.next()) {
                UserDto user = new UserDto();
                user.setUserId(res.getInt("id"));
                user.setUsername(res.getString("UserName"));
                user.setPassword(res.getString("Password"));
                user.setAge(res.getInt("UseType"));
                user.setRealName(res.getString("RealName"));
                user.setDeptID(res.getInt("DeptID"));
                return user;
            } else {
                return null;
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源,倒关
            try {
                if (res != null) res.close();
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
            }
        }
    }
}